package cn.myjerry.test.JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Random;

/**
 * 测试时间处理(java.sql.Date, Time, Timestamp),取出指定时间段的数据
 * @author Jerry
 *
 */
public class Demo07 {
	// JDBC 驱动名及数据库 URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
    static final String DB_URL = "jdbc:mysql://localhost:3306/testjdbc?useSSL=true&useUnicode=true&characterEncoding=utf8";
    
    // 数据库的用户名与密码，需要根据自己的设置
    static final String USER = "root";
    static final String PASS = "Jerry1912317";
	
    /**
     * 将字符串代表的时间转为long数字（格式：yyyy-MM-dd hh:mm:ss）
     * @param dataStr
     * @return
     */
    public static long str2Date (String dataStr) {
    		DateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
    		try {
				return format.parse(dataStr).getTime();
			} catch (ParseException e) {
				e.printStackTrace();
				return 0;
			}
    }
    
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		// 加载驱动类
		try {
			Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(DB_URL, USER, PASS);
			
			ps = conn.prepareStatement("SELECT * FROM User WHERE LastLoginTime > ? AND LastLoginTime < ?");

			java.sql.Date start = new java.sql.Date(str2Date("2018-04-22 08:01:45"));
			java.sql.Date end = new java.sql.Date(str2Date("2018-04-24 08:01:45"));
			
			ps.setDate(1, start);
			ps.setDate(2, end);
			
			res = ps.executeQuery();

			while(res.next()) {
				System.out.println(res.getInt("Id") + "==>" + res.getString("Name") + "==>" + res.getString("Mobile") + "==>" + res.getString("PassWord") + "==>"
						+ res.getString("CreateTime") + "==>" + res.getString("LastLoginTime"));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(res != null) {
				try {
					res.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}
